﻿Imports System.Data.SqlClient
Imports System.IO
Imports System.Globalization

Public Class Functions
    Public Shared Function ConvertTransCode2Name(ByVal ObjType As String) As String
        Dim TransName As String = ""
        Select Case ObjType
            Case "59"
                TransName = "Receipt From Prod."

        End Select
        Return TransName

    End Function
    Public Shared Sub WriteLog(ByVal Str As String)
        Dim oWrite As IO.StreamWriter
        Dim FilePath As String
        FilePath = Application.StartupPath + "\logfile.txt"

        If IO.File.Exists(FilePath) Then
            oWrite = IO.File.AppendText(FilePath)
        Else
            oWrite = IO.File.CreateText(FilePath)
        End If
        oWrite.Write(Now.ToString() + ":" + Str + vbCrLf)
        oWrite.Close()
    End Sub
    Public Shared Function ExportGridEx(ByVal Grid As Janus.Windows.GridEX.GridEX) As Boolean
        Dim Export As New Janus.Windows.GridEX.Export.GridEXExporter
        Export.GridEX = Grid

        Dim SaveFileDialog1 As New SaveFileDialog()
        SaveFileDialog1.Filter = "Excel files (*.xls,*.xlsx,*.xml)|*.*"
        SaveFileDialog1.InitialDirectory = "C:\"
            SaveFileDialog1.FileName = "Export.xls"

        SaveFileDialog1.DefaultExt = "xml"

        If SaveFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
            Dim ReportFile As String = SaveFileDialog1.FileName
            Dim stream As New FileStream(ReportFile, FileMode.Create)
            Try
                Export.IncludeHeaders = True
                Export.ExportMode = Janus.Windows.GridEX.ExportMode.AllRows
                Export.IncludeExcelProcessingInstruction = True
                Export.Export(stream)

                stream.Flush()
                MessageBox.Show("Export completed!")
            Catch ex As Exception
                MessageBox.Show(ex.ToString)
                Return False
            Finally
                stream.Dispose()
            End Try
            Return True
        Else
            Return False
        End If

    End Function
    Public Shared Function DoQueryReturnDT(ByVal query As String) As DataTable
        Dim dt As DataTable
        Dim oRecordSet As SAPbobsCOM.Recordset
        oRecordSet = PublicVariable.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
        Try
            oRecordSet.DoQuery(query)
            If oRecordSet.RecordCount > 0 Then
                dt = ConvertRS2DT(oRecordSet)
                Return dt
            Else
                Return Nothing
            End If
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
            Return Nothing
        End Try
    End Function
    Public Shared Function ConvertRS2DT(ByVal RS As SAPbobsCOM.Recordset) As DataTable
        Dim dtTable As New DataTable
        Dim NewCol As DataColumn
        Dim NewRow As DataRow
        Dim ColCount As Integer
        Try
            For ColCount = 0 To RS.Fields.Count - 1
                Dim dataType As String = "System."
                Select Case RS.Fields.Item(ColCount).Type
                    Case SAPbobsCOM.BoFieldTypes.db_Alpha
                        dataType = dataType & "String"
                    Case SAPbobsCOM.BoFieldTypes.db_Date
                        dataType = dataType & "DateTime"
                    Case SAPbobsCOM.BoFieldTypes.db_Float
                        dataType = dataType & "Double"
                    Case SAPbobsCOM.BoFieldTypes.db_Memo
                        dataType = dataType & "String"
                    Case SAPbobsCOM.BoFieldTypes.db_Numeric
                        dataType = dataType & "Decimal"
                    Case Else
                        dataType = dataType & "String"
                End Select

                NewCol = New DataColumn(RS.Fields.Item(ColCount).Name, System.Type.GetType(dataType))
                dtTable.Columns.Add(NewCol)
            Next

            Do Until RS.EoF

                NewRow = dtTable.NewRow
                'populate each column in the row we're creating
                For ColCount = 0 To RS.Fields.Count - 1

                    NewRow.Item(RS.Fields.Item(ColCount).Name) = RS.Fields.Item(ColCount).Value

                Next

                'Add the row to the datatable
                dtTable.Rows.Add(NewRow)

                RS.MoveNext()
            Loop
            Return dtTable
        Catch ex As Exception
            MsgBox(ex.ToString & Chr(10) & "Error converting SAP Recordset to DataTable", MsgBoxStyle.Exclamation)
            Return Nothing
        End Try
    End Function
#Region "ADO SAP Local"
    Public Shared Function SAP_Local_OpenSQLConnection() As SqlConnection
        Dim dt As DataTable = DoQueryReturnDT("Select * from _OCNT")
        If dt.Rows.Count > 0 Then
            Dim MyArr As Array = oMD5.DecryptPassword(dt.Rows(0).Item("CnnStr").ToString).Split(";")
            Dim SAPConnection As SqlConnection = New SqlConnection
            SAPConnection.ConnectionString = "server= " + MyArr(3).ToString() + ";database=" + MyArr(0).ToString() + " ;uid=" + MyArr(4).ToString() + "; pwd=" + MyArr(5).ToString() + ";"
            Try
                SAPConnection.Open()
                Return SAPConnection
            Catch ex As Exception
                Functions.WriteLog(ex.Message)
                Return Nothing
            End Try
        Else
            WriteLog("no connection setup")
            Return Nothing
        End If

    End Function
    Public Shared Function SAP_Local_RunQuery(ByVal querystr As String) As DataTable
        Dim SAPConnection As SqlConnection = New SqlConnection
        Try
            SAPConnection = SAP_Local_OpenSQLConnection()
            If Not IsNothing(SAPConnection) Then
                Dim MyCommand As SqlCommand = New SqlCommand(querystr, SAPConnection)
                MyCommand.CommandType = CommandType.Text
                Dim da As SqlDataAdapter = New SqlDataAdapter()
                Dim mytable As DataTable = New DataTable()
                da.SelectCommand = MyCommand
                da.SelectCommand.CommandTimeout = 240
                da.Fill(mytable)

                If mytable Is Nothing Then Return New DataTable
                Return mytable
            Else
                Return New DataTable
            End If
        Catch ex As Exception
            Functions.WriteLog(ex.Message + vbCrLf + querystr)
            Return New DataTable
        Finally
            If Not SAPConnection Is Nothing Then
                SAPConnection.Close()
            End If
        End Try
    End Function
    Public Shared Function SAP_Local_RunQuery_NoResult(ByVal querystr As String) As String
        Dim SAPConnection As SqlConnection = Nothing
        Try
            SAPConnection = SAP_Local_OpenSQLConnection()
            If Not IsNothing(SAPConnection) Then
                Dim MyCommand As SqlCommand = New SqlCommand(querystr, SAPConnection)
                MyCommand.CommandType = CommandType.Text
                Dim mytable As DataTable = New DataTable()
                MyCommand.CommandTimeout = 240
                MyCommand.ExecuteNonQuery()
            Else
                Return "Connection Error"
            End If
        Catch sqlEx As SqlException
            Functions.WriteLog(sqlEx.Message + " -- " + querystr)
            Return sqlEx.Message
        Catch ex As Exception
            Functions.WriteLog(ex.Message)
            Return ex.Message
        Finally
            If Not SAPConnection Is Nothing Then
                SAPConnection.Close()
            End If
        End Try

        Return ""
    End Function
#End Region

#Region "Build Table Structure"
    ''' <param name="tableName">table name , example : T </param>
    ''' <param name="fieldName">list of fields, example: A;B;C;D.</param>
    Public Shared Function BuildTable(ByVal tableName As String, ByVal fieldName As String) As DataTable
        Dim dt As New DataTable(tableName)
        If fieldName <> "" Then
            Dim arrFieldName As Array = fieldName.Split(";")
            For i As Integer = 0 To arrFieldName.Length - 1
                dt.Columns.Add(arrFieldName(i).ToString)
            Next
        End If
        Return dt
    End Function
#End Region
End Class
